﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class DALComercio : DALBase
    {
        private static string cmdAltaSucursales = "Insert into ComercioSucursales values(@ComId, @ComSucursal)";
        private static string cmdAltaUsuario = "Insert into Usuarios values(@UsuNombre,@UsuPassword,@Rol)select scope_identity()";
        private static string cmdAltaRubros = "Insert into ComercioRubros values(@ComId,@RubId)";
        private static string cmdAltaTags = "Insert into Tags values(@TagDescripcion, @ComId)";
        private static string cmdAltaRedesSociales = "Insert into ComerciosRedesSociales values(@ComId, @RedId, @RedDescripcion)";
        private static string cmdObtenerComercioPorUsuario = "Select * From Comercios Where UsuId = @UsuId";
        private static string cmdObtenerComercio = "Select * From Comercios Where ComNombre = @ComNombre";
        private static string cmdObtenerSucursales = "Select * From Comercios Where ComId IN (Select ComSucursal From ComercioSucursales Where ComId = @ComId)";
        private static string cmdModificarUsuario = "Update Usuarios Set UsuNombre = @UsuNombre, UsuPassword = @UsuPassword Where UsuId = @UsuId";
        private static string cmdObtenerTodosLosComercios = "Select ComNombre From Comercios Where ComNombre like @ComNombre ";
        private static string cmdModificarComercio = " Update Comercios Set ComRUT = @ComRUT, ComNombre = @ComNombre, ComDireccion = @ComDireccion, ComTelefono = @ComTelefono, ComWeb = @ComWeb, ComCoordenadas = @ComCoordenadas, ComEmail = @ComEmail, ComAnuncio = @ComAnuncio, BarId = @BarId Where ComId = @ComId ";
        private static string cmdBorrarSucursales = "DELETE FROM ComercioSucursales WHERE ComId = @ComId";
        private static string cmdBorrarRubros = "DELETE FROM ComercioRubros WHERE ComId = @ComId";
        private static string cmdBorrarTags = "DELETE FROM Tags WHERE ComId = @ComId";
        private static string cmdBorrarRedes = "DELETE FROM ComerciosRedesSociales WHERE ComId = @ComId";
        private static string cmdSucursalesDeComercio = "select c.ComId,c.ComRUT, c.ComNombre,c.ComDireccion,c.ComTelefono,c.ComWeb,c.ComCoordenadas,c.ComLogo,c.ComEmail, c.ComAnuncio,c.BarId from ComercioSucursales s join Comercios c on s.ComSucursal = c.ComId where s.ComId = @ComId";
        private static string cmdComercioPorId = "select * from Comercios where ComId = @ComId";
        private static string cmdComerciosMejorPuntuados = "select * from Comercios where ComId in(select TOP 10 (select AVG(Calificacion) from Calificaciones where Calificaciones.ComId=Comercios.ComId) as calificacion from Comercios order by calificacion DESC)";
        private static string cmdComerciosDestacados = "select * from comercios where Destacado= 1 ";



        public SqlDataReader ComerciosDestacados()
        {
            List<SqlParameter> lstparameters = new List<SqlParameter>();
            SqlDataReader dr = select(lstparameters, cmdComerciosDestacados, CommandType.Text);
            return dr;
        }

        public SqlDataReader ComerciosMejorPuntuados()
        {
            List<SqlParameter> lstparameters = new List<SqlParameter>();
            SqlDataReader dr = select(lstparameters, cmdComerciosMejorPuntuados, CommandType.Text);
            return dr;
        }

        public SqlDataReader BuscarSucursales(int ComercioId)
        {
            List<SqlParameter> lstparameters = new List<SqlParameter>();
            lstparameters.Add(new SqlParameter("@ComId", ComercioId));
            SqlDataReader dr = select(lstparameters, cmdSucursalesDeComercio, CommandType.Text);
            return dr;
        }
        public SqlDataReader buscarComercioPorId(int id)
        {
            List<SqlParameter> lstparameters = new List<SqlParameter>();
            lstparameters.Add(new SqlParameter("@ComId", id));
            SqlDataReader dr = select(lstparameters, cmdComercioPorId, CommandType.Text);
            return dr;
        }
        public static int AltaComercio(Hashtable hashComercio, Hashtable hashUsuario, List<int> sucursales, List<int> rubros, List<string> tags, List<Hashtable> hashsRedesSociales)
        {
            int idusu = -1;
            int idCom = -1;
            SqlConnection con = null;
            SqlTransaction trn = null;
            try
            {
                con = abrirConexion();
                trn = con.BeginTransaction();
                List<SqlParameter> parametrosUsuario = new List<SqlParameter>();
                parametrosUsuario.Add(new SqlParameter("@UsuNombre", hashUsuario["UsuarioNombre"]));
                parametrosUsuario.Add(new SqlParameter("@UsuPassword", hashUsuario["UsuarioPassword"]));
                parametrosUsuario.Add(new SqlParameter("@Rol", hashUsuario["Rol"]));
                idusu = Convert.ToInt32(ejecutaScalar(cmdAltaUsuario, parametrosUsuario, con, trn, CommandType.Text));
                if (idusu > 0)
                {
                    string cmdAltaComercio = "Insert into Comercios values(";
                    List<SqlParameter> parametrosComercio = new List<SqlParameter>();
                    parametrosComercio.Add(new SqlParameter("@ComRUT", hashComercio["ComRUT"]));
                    parametrosComercio.Add(new SqlParameter("@ComNombre", hashComercio["ComNombre"]));
                    parametrosComercio.Add(new SqlParameter("@ComDireccion", hashComercio["ComDireccion"]));
                    parametrosComercio.Add(new SqlParameter("@ComTelefono", hashComercio["ComTelefono"]));
                    cmdAltaComercio += "@ComRUT,@ComNombre,@ComDireccion,@ComTelefono,";

                    if (string.IsNullOrEmpty(Convert.ToString(hashComercio["ComWeb"])))
                    {
                        cmdAltaComercio += "null,";
                    }
                    else
                    {
                        cmdAltaComercio += "@ComWeb,";
                        parametrosComercio.Add(new SqlParameter("@ComWeb", hashComercio["ComWeb"]));
                    }
                    cmdAltaComercio += "@ComCoordenadas,";
                    parametrosComercio.Add(new SqlParameter("@ComCoordenadas", hashComercio["ComCoordenadas"]));

                    if (!string.IsNullOrEmpty(Convert.ToString(hashComercio["ComLogo"])))
                    {
                        cmdAltaComercio += "@ComLogo,";
                        parametrosComercio.Add(new SqlParameter("@ComLogo", hashComercio["ComLogo"]));
                    }
                    else
                    {
                        cmdAltaComercio += "null,";
                    }


                    if (string.IsNullOrEmpty(Convert.ToString(hashComercio["ComEmail"])))
                    {
                        cmdAltaComercio += "null,";
                    }
                    else
                    {
                        cmdAltaComercio += "@ComEmail,";
                        parametrosComercio.Add(new SqlParameter("@ComEmail", hashComercio["ComEmail"]));
                    }

                    parametrosComercio.Add(new SqlParameter("@ComAnuncio", hashComercio["ComAnuncio"]));
                    parametrosComercio.Add(new SqlParameter("@UsuId", idusu));
                    parametrosComercio.Add(new SqlParameter("@BarId", hashComercio["BarId"]));
                    parametrosComercio.Add(new SqlParameter("@Destacado", "0"));

                    cmdAltaComercio += "@ComAnuncio,@UsuId,@BarId, @Destacado) select scope_identity()";
                    idCom = Convert.ToInt32(ejecutaScalar(cmdAltaComercio, parametrosComercio, con, trn, CommandType.Text));
                    if (idCom > 0)
                    {
                        foreach (int suc in sucursales)
                        {
                            List<SqlParameter> parametrosSucursales = new List<SqlParameter>();
                            parametrosSucursales.Add(new SqlParameter("@ComId", idCom));
                            parametrosSucursales.Add(new SqlParameter("@ComSucursal", suc));
                            int resultado = ejecutaNonQuery(cmdAltaSucursales, parametrosSucursales, con, trn, CommandType.Text);
                            if (resultado <= 0)
                            {
                                throw new Exception();
                            }
                        }
                        foreach (int rub in rubros)
                        {
                            List<SqlParameter> parametrosRubros = new List<SqlParameter>();
                            parametrosRubros.Add(new SqlParameter("@ComId", idCom));
                            parametrosRubros.Add(new SqlParameter("@RubId", rub));
                            int resultado = ejecutaNonQuery(cmdAltaRubros, parametrosRubros, con, trn, CommandType.Text);
                            if (resultado <= 0)
                            {
                                throw new Exception();
                            }
                        }
                        foreach (string tag in tags)
                        {
                            List<SqlParameter> parametrosTags = new List<SqlParameter>();
                            parametrosTags.Add(new SqlParameter("@TagDescripcion", tag));
                            parametrosTags.Add(new SqlParameter("@ComId", idCom));
                            int tagId = ejecutaNonQuery(cmdAltaTags, parametrosTags, con, trn, CommandType.Text);
                            if (tagId <= 0)
                            {
                                throw new Exception();
                            }
                        }
                        foreach (Hashtable hash in hashsRedesSociales)
                        {
                            List<SqlParameter> parametrosRedesSociales = new List<SqlParameter>();
                            parametrosRedesSociales.Add(new SqlParameter("@RedId", Convert.ToInt32(hash["RedId"])));
                            parametrosRedesSociales.Add(new SqlParameter("@RedDescripcion", hash["RedDescripcion"]));
                            parametrosRedesSociales.Add(new SqlParameter("@ComId", idCom));
                            int tagId = ejecutaNonQuery(cmdAltaRedesSociales, parametrosRedesSociales, con, trn, CommandType.Text);
                            if (tagId <= 0)
                            {
                                throw new Exception();
                            }
                        }

                    }
                    else
                    {
                        throw new Exception();
                    }
                }
                else
                {
                    throw new Exception();
                }

                trn.Commit();
            }
            catch (SqlException ex)
            {
                trn.Rollback();
            }
            catch (Exception e)
            {
                trn.Rollback();
            }
            finally
            {
                trn.Dispose();
                cerrarConexion(con);
            }
            return idCom;
        }
        public static SqlDataReader ObtenerComercioPorNombre(String nombre)
        {
            SqlConnection connection = new SqlConnection();
            SqlCommand commandObtenerComercio = new SqlCommand();
            SqlDataReader drResults;
            connection.ConnectionString = GetConnectionString();
            connection.Open();
            commandObtenerComercio.Parameters.Add(new SqlParameter("@ComNombre", nombre));
            commandObtenerComercio.Connection = connection;
            commandObtenerComercio.CommandText = cmdObtenerComercio;
            commandObtenerComercio.CommandType = CommandType.Text;

            drResults = commandObtenerComercio.ExecuteReader(CommandBehavior.CloseConnection);
            return drResults;
        }
        public static SqlDataReader ObtenerComercioPorUsuario(int usuId)
        {
            SqlConnection connection = new SqlConnection();
            SqlCommand commandObtenerComercio = new SqlCommand();
            SqlDataReader drResults;
            connection.ConnectionString = GetConnectionString();
            connection.Open();
            commandObtenerComercio.Parameters.Add(new SqlParameter("@UsuId", usuId));
            commandObtenerComercio.Connection = connection;
            commandObtenerComercio.CommandText = cmdObtenerComercioPorUsuario;
            commandObtenerComercio.CommandType = CommandType.Text;

            drResults = commandObtenerComercio.ExecuteReader(CommandBehavior.CloseConnection);
            return drResults;
        }
        public static SqlDataReader ObtenerComercios(string prefix)
        {
            SqlConnection connection = new SqlConnection();
            SqlCommand commandObtenerComercios = new SqlCommand();
            SqlDataReader drResults;
            connection.ConnectionString = GetConnectionString();
            connection.Open();
            commandObtenerComercios.Parameters.Add(new SqlParameter("@ComNombre", "%" + prefix + "%"));
            commandObtenerComercios.Connection = connection;
            commandObtenerComercios.CommandText = cmdObtenerTodosLosComercios;
            commandObtenerComercios.CommandType = CommandType.Text;

            drResults = commandObtenerComercios.ExecuteReader(CommandBehavior.CloseConnection);
            return drResults;
        }
        public static SqlDataReader ObtenerCoordenadasFiltros(int _Barrio, int _Rubro, string _Tag)
        {
            string filtro = "";
            SqlConnection connection = new SqlConnection();
            SqlCommand commandObtenerComercios = new SqlCommand();
            SqlDataReader drResults;
            connection.ConnectionString = GetConnectionString();
            connection.Open();
            if (_Barrio > 0)
            {
                filtro = "Where Comercios.BarId =  @BarId";
                commandObtenerComercios.Parameters.Add(new SqlParameter("@BarId", _Barrio));
            }
            if (_Rubro > 0)
            {
                if (string.IsNullOrEmpty(filtro))
                {
                    filtro = "Where ComercioRubros.RubId =  @RubId";
                }
                else
                {
                    filtro += " And ComercioRubros.RubId =  @RubId";
                }
                commandObtenerComercios.Parameters.Add(new SqlParameter("@RubId", _Rubro));
            }
            if (!string.IsNullOrEmpty(_Tag))
            {
                if (string.IsNullOrEmpty(filtro))
                {
                    filtro = "Where Tags.TagDescripcion like  @Tag";
                }
                else
                {
                    filtro += " And Tags.TagDescripcion like  @Tag";
                }
                commandObtenerComercios.Parameters.Add(new SqlParameter("@Tag", "%" + _Tag + "%"));
            }

            commandObtenerComercios.Connection = connection;
            string cmdCom = "Select DISTINCT Comercios.ComId, Comercios.ComDireccion, Comercios.ComNombre, Comercios.ComTelefono, Comercios.ComCoordenadas " +
                            " From Comercios " +
                            " Left Join ComercioRubros On Comercios.ComId = ComercioRubros.ComId " +
                            " Left Join Tags On Comercios.ComId = Tags.ComId " + filtro;
            commandObtenerComercios.CommandText = cmdCom;
            commandObtenerComercios.CommandType = CommandType.Text;

            drResults = commandObtenerComercios.ExecuteReader(CommandBehavior.CloseConnection);
            return drResults;

        }
        public static SqlDataReader ObtenerSucursales(int ComId)
        {
            SqlConnection connection = new SqlConnection();
            SqlCommand commandObtenerSucursales = new SqlCommand();
            SqlDataReader drResults;
            connection.ConnectionString = GetConnectionString();
            connection.Open();
            commandObtenerSucursales.Parameters.Add(new SqlParameter("@ComId", ComId));
            commandObtenerSucursales.Connection = connection;
            commandObtenerSucursales.CommandText = cmdObtenerSucursales;
            commandObtenerSucursales.CommandType = CommandType.Text;

            drResults = commandObtenerSucursales.ExecuteReader(CommandBehavior.CloseConnection);
            return drResults;
        }
        public static int ModificarComercio(int idComercio, Hashtable hashComercio, Hashtable hashUsuario, List<int> sucursales, List<int> rubros, List<string> tags, List<Hashtable> hashsRedesSociales)
        {
            int idusu = -1;
            int resCom = -1;
            int retorno = 1;
            SqlConnection con = null;
            SqlTransaction trn = null;
            try
            {
                con = abrirConexion();
                trn = con.BeginTransaction();
                List<SqlParameter> parametrosUsuario = new List<SqlParameter>();
                parametrosUsuario.Add(new SqlParameter("@UsuNombre", hashUsuario["UsuarioNombre"]));
                parametrosUsuario.Add(new SqlParameter("@UsuPassword", hashUsuario["UsuarioPassword"]));
                parametrosUsuario.Add(new SqlParameter("@UsuId", hashUsuario["UsuarioId"]));

                idusu = ejecutaNonQuery(cmdModificarUsuario, parametrosUsuario, con, trn, CommandType.Text);
                if (idusu > 0)
                {
                    List<SqlParameter> parametrosComercio = new List<SqlParameter>();
                    parametrosComercio.Add(new SqlParameter("@ComId", idComercio));
                    parametrosComercio.Add(new SqlParameter("@ComRUT", hashComercio["ComRUT"]));
                    parametrosComercio.Add(new SqlParameter("@ComNombre", hashComercio["ComNombre"]));
                    parametrosComercio.Add(new SqlParameter("@ComDireccion", hashComercio["ComDireccion"]));
                    parametrosComercio.Add(new SqlParameter("@ComTelefono", hashComercio["ComTelefono"]));
                    parametrosComercio.Add(new SqlParameter("@ComWeb", hashComercio["ComWeb"]));
                    parametrosComercio.Add(new SqlParameter("@ComCoordenadas", hashComercio["ComCoordenadas"]));
                    if (!string.IsNullOrEmpty(Convert.ToString(hashComercio["ComLogo"])))
                    {
                        parametrosComercio.Add(new SqlParameter("@ComLogo", hashComercio["ComLogo"]));
                    }
                    parametrosComercio.Add(new SqlParameter("@ComEmail", hashComercio["ComEmail"]));
                    parametrosComercio.Add(new SqlParameter("@ComAnuncio", hashComercio["ComAnuncio"]));
                    parametrosComercio.Add(new SqlParameter("@BarId", hashComercio["BarId"]));
                    resCom = ejecutaNonQuery(cmdModificarComercio, parametrosComercio, con, trn, CommandType.Text);
                    if (resCom > 0)
                    {
                        //Elimino sucursales
                        List<SqlParameter> parametrosEliminarSucursales = new List<SqlParameter>();
                        parametrosEliminarSucursales.Add(new SqlParameter("@ComId", idComercio));
                        ejecutaNonQuery(cmdBorrarSucursales, parametrosEliminarSucursales, con, trn, CommandType.Text);

                        foreach (int suc in sucursales)
                        {
                            // doy de alta sucursales
                            List<SqlParameter> parametrosSucursales = new List<SqlParameter>();
                            parametrosSucursales.Add(new SqlParameter("@ComId", idComercio));
                            parametrosSucursales.Add(new SqlParameter("@ComSucursal", suc));
                            int resultado = ejecutaNonQuery(cmdAltaSucursales, parametrosSucursales, con, trn, CommandType.Text);
                            if (resultado <= 0)
                            {
                                retorno = -1;
                                throw new Exception();
                            }
                        }
                        //Elimino rubros
                        List<SqlParameter> parametrosEliminarRubros = new List<SqlParameter>();
                        parametrosEliminarRubros.Add(new SqlParameter("@ComId", idComercio));
                        ejecutaNonQuery(cmdBorrarRubros, parametrosEliminarRubros, con, trn, CommandType.Text);

                        foreach (int rub in rubros)
                        {
                            List<SqlParameter> parametrosRubros = new List<SqlParameter>();
                            parametrosRubros.Add(new SqlParameter("@ComId", idComercio));
                            parametrosRubros.Add(new SqlParameter("@RubId", rub));
                            int resultado = ejecutaNonQuery(cmdAltaRubros, parametrosRubros, con, trn, CommandType.Text);
                            if (resultado <= 0)
                            {
                                retorno = -1;
                                throw new Exception();
                            }
                        }
                        //Elimino tags
                        List<SqlParameter> parametrosEliminarTags = new List<SqlParameter>();
                        parametrosEliminarTags.Add(new SqlParameter("@ComId", idComercio));
                        ejecutaNonQuery(cmdBorrarTags, parametrosEliminarTags, con, trn, CommandType.Text);

                        foreach (string tag in tags)
                        {
                            List<SqlParameter> parametrosTags = new List<SqlParameter>();
                            parametrosTags.Add(new SqlParameter("@TagDescripcion", tag));
                            parametrosTags.Add(new SqlParameter("@ComId", idComercio));
                            int tagId = ejecutaNonQuery(cmdAltaTags, parametrosTags, con, trn, CommandType.Text);
                            if (tagId <= 0)
                            {
                                throw new Exception();
                            }
                        }

                        //Elimino redessociales
                        List<SqlParameter> parametrosEliminarRedes = new List<SqlParameter>();
                        parametrosEliminarRedes.Add(new SqlParameter("@ComId", idComercio));
                        ejecutaNonQuery(cmdBorrarRedes, parametrosEliminarRedes, con, trn, CommandType.Text);

                        foreach (Hashtable hash in hashsRedesSociales)
                        {
                            List<SqlParameter> parametrosRedesSociales = new List<SqlParameter>();
                            parametrosRedesSociales.Add(new SqlParameter("@RedId", Convert.ToInt32(hash["RedId"])));
                            parametrosRedesSociales.Add(new SqlParameter("@RedDescripcion", hash["RedDescripcion"]));
                            parametrosRedesSociales.Add(new SqlParameter("@ComId", idComercio));
                            int tagId = ejecutaNonQuery(cmdAltaRedesSociales, parametrosRedesSociales, con, trn, CommandType.Text);
                            if (tagId <= 0)
                            {
                                throw new Exception();
                            }
                        }

                    }
                    else
                    {
                        throw new Exception();
                    }
                }
                else
                {
                    throw new Exception();
                }

                trn.Commit();
            }
            catch (SqlException ex)
            {
                trn.Rollback();
            }
            catch (Exception e)
            {
                trn.Rollback();
            }
            finally
            {
                trn.Dispose();
                cerrarConexion(con);
            }
            return resCom;
        }



        public static SqlDataReader ObtenerCoordenadasAll()
        {
            SqlConnection connection = new SqlConnection();
            SqlCommand commandObtenerCoordenadasAll = new SqlCommand();
            SqlDataReader drResults;
            connection.ConnectionString = GetConnectionString();
            connection.Open();
            //  commandObtenerCoordenadasAll.Parameters.Add(new SqlParameter("@ComId", ComId));
            commandObtenerCoordenadasAll.Connection = connection;
            commandObtenerCoordenadasAll.CommandText = "Select * from Comercios";
            commandObtenerCoordenadasAll.CommandType = CommandType.Text;

            drResults = commandObtenerCoordenadasAll.ExecuteReader(CommandBehavior.CloseConnection);
            return drResults;
        }

        public static SqlDataReader ObtenerCoordenadasComerciosPrincipal(string filtro)
        {
            SqlConnection connection = new SqlConnection();
            SqlCommand commandObtenerComercios = new SqlCommand();
            SqlDataReader drResults;
            connection.ConnectionString = GetConnectionString();
            connection.Open();
            commandObtenerComercios.Parameters.Add(new SqlParameter("@filtro", "%" + filtro + "%"));
            commandObtenerComercios.Connection = connection;
            string cmdCom = " Select DISTINCT Comercios.ComId, Comercios.ComDireccion, Comercios.ComNombre, Comercios.ComTelefono, Comercios.ComCoordenadas " +
                           "From Comercios " +
                           "Left Join ComercioRubros On Comercios.ComId = ComercioRubros.ComId " +
                           "Left Join Rubros On ComercioRubros.RubId = Rubros.RubId " +
                           "Left Join Productos On Comercios.ComId = Productos.ComId " +
                           "Left Join Tags On Comercios.ComId = Tags.ComId " +
                           "Where Comercios.ComNombre like @filtro " +
                           "or Rubros.RubNombre like @filtro " +
                           "or Productos.ProNombre like @filtro  " +
                           "or Tags.TagDescripcion like @filtro ";
            commandObtenerComercios.CommandText = cmdCom;
            commandObtenerComercios.CommandType = CommandType.Text;
            drResults = commandObtenerComercios.ExecuteReader(CommandBehavior.CloseConnection);
            return drResults;
        }

        public static SqlDataReader ObtenerComerciosAll()
        {

            SqlConnection connection = new SqlConnection();
            SqlCommand commandObtenerComercios = new SqlCommand();
            SqlDataReader drResults;
            connection.ConnectionString = GetConnectionString();
            connection.Open();
            commandObtenerComercios.Connection = connection;
            string cmdCom = "Select * from comercios";
            commandObtenerComercios.CommandText = cmdCom;
            commandObtenerComercios.CommandType = CommandType.Text;
            drResults = commandObtenerComercios.ExecuteReader(CommandBehavior.CloseConnection);
            return drResults;
        }

        public static SqlDataReader ObtenerDescripcion(int ComId)
        {
            List<SqlParameter> lstparameters = new List<SqlParameter>();
            lstparameters.Add(new SqlParameter("@ComId", ComId));
            string cmdCom = "Select ComDescripcion from comercios Where ComId = @ComId";
            SqlDataReader dr = select(lstparameters, cmdCom, CommandType.Text);
            return dr;
        }
    }
}
